﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.OleDb;
using System.Data;
using DataCollector.Entity;

namespace DataCollector.Dao
{
    public class WriteMonitorDataDao : IDisposable
    {

        public string DbFileName
        {
            get;
            private set;
        }

        private DbConnection Conn
        {
            get;
            set;
        }

        /// <summary>
        /// 判断数据库是否打开
        /// </summary>
        public bool IsOpen
        {
            get { return Conn != null && Conn.State != ConnectionState.Closed; }
        }

        /// <summary>
        /// 事务
        /// </summary>
        private DbTransaction _transaction = null;

        /// <summary>
        /// 创建并返回与当前连接关联的 DbCommand 对象。
        /// 如果事务已启动，则绑定启动的事务。
        /// </summary>
        /// <returns>一个 DbCommand 对象</returns>
        private DbCommand CreateCommand()
        {
            DbCommand cmd = Conn.CreateCommand();
            if (_transaction != null)
                cmd.Transaction = _transaction;
            return cmd;
        }

        /// <summary>
        /// 生成 AccessSeegeneDao
        /// </summary>
        /// <param name="dbFileName"></param>
        public WriteMonitorDataDao(string dbFileName)
        {
            this.DbFileName = dbFileName;
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn = null;
            }
        }

        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open()
        {
            if (Conn != null)
                throw new InvalidOperationException("Connection has opened");
            Conn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;", DbFileName));
            Conn.Open();
        }

        /// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="command"></param>
        /// <param name="ParameterName"></param>
        /// <param name="value"></param>
        private void AddDbParameter(DbCommand command, string parameterName, object value)
        {
            OleDbParameter dbParam = new OleDbParameter();
            dbParam.ParameterName = parameterName;
            dbParam.Value = value;
            command.Parameters.Add(dbParam);
        }

        /// <summary>
        /// yyMMddHHmmss -> DateTime
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private DateTime DecodeDateTime(string value)
        {
            if (value.Length == 12)
                value = "20" + value;
            if (value.Length != 14)
                throw new ArgumentException("value");
            return DateTime.ParseExact(value, "yyyyMMddHHmmss", null);
        }

        /// <summary>
        /// DateTime -> yyMMddHHmmss
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private string EncodeDateTime(DateTime value)
        {
            return value.ToString("yyMMddHHmmss");
        }

        #region ISeegeneDao 成员
        /// <summary>
        /// 启动事务
        /// </summary>
        public void BeginTransaction()
        {
            if (_transaction != null)
                throw new InvalidOperationException("Transaction has begin.");
            _transaction = Conn.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (_transaction == null)
                throw new InvalidOperationException("Transaction has not begin.");
            _transaction.Commit();
            _transaction = null;
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (_transaction == null)
                throw new InvalidOperationException("Transaction has not begin.");
            _transaction.Rollback();
            _transaction = null;
        }
        #endregion

        public DBSelectResult ReadLastData()
        {
            lock (lockObject)
            {

                if (SelectLastCommand == null)
                {
                    SelectLastCommand = CreateCommand();
                    SelectLastCommand.CommandText = seleteSql;
                }
                DBSelectResult mrRet = null;
                DbDataReader ddr = SelectLastCommand.ExecuteReader();
                if (ddr.Read())
                {
                    mrRet = new DBSelectResult();
                    mrRet.Id = ddr.GetInt32(0);
                    mrRet.Humidity = ddr.GetDouble(1);
                    mrRet.Illumination = ddr.GetDouble(2);
                    mrRet.Temperature = ddr.GetDouble(3);
                    mrRet.ExportTime = ddr.GetString(4);
                }
                ddr.Close();
                return mrRet;
            }
        }

        string seleteSql = "SELECT * FROM Table2 order by id desc";
        private string sqlStr1 = "Insert INTO Table1 (humidity,illumination,temperature,export_time) VALUES (:humidity,:illumination,:temperature,:export_time)";
        private string sqlStr2 = " Insert INTO Table2 (humidity,illumination,temperature,export_time) VALUES (:humidity,:illumination,:temperature,:export_time)";
        private string sqlStr2Del = "delete from Table2";
        private DbCommand InsertTable1Command = null;
        private DbCommand InsertTable2Command = null;
        private DbCommand SelectLastCommand = null;
        private DbCommand DelTable2Command = null;
        private object lockObject = new object();

        public void Write(MonitorResult mr)
        {
            lock (lockObject)
            {
                if (InsertTable1Command == null)
                {
                    InsertTable1Command = CreateCommand();
                    InsertTable1Command.CommandText = sqlStr1;
                }
                InsertTable1Command.Parameters.Clear();
                AddDbParameter(InsertTable1Command, "humidity", mr.Humidity);
                AddDbParameter(InsertTable1Command, "illumination", mr.Illumination);
                AddDbParameter(InsertTable1Command, "temperature", mr.Temperature);
                AddDbParameter(InsertTable1Command, "export_time", DateTime.Now.ToString("yyyyMMddhhmmss"));
                InsertTable1Command.ExecuteNonQuery();

                if (DelTable2Command == null)
                {
                    DelTable2Command = CreateCommand();
                    DelTable2Command.CommandText = sqlStr2Del;
                }
                DelTable2Command.ExecuteNonQuery();

                if (InsertTable2Command == null)
                {
                    InsertTable2Command = CreateCommand();
                    InsertTable2Command.CommandText = sqlStr2;
                }
                InsertTable2Command.Parameters.Clear();
                AddDbParameter(InsertTable2Command, "humidity", mr.Humidity);
                AddDbParameter(InsertTable2Command, "illumination", mr.Illumination);
                AddDbParameter(InsertTable2Command, "temperature", mr.Temperature);
                AddDbParameter(InsertTable2Command, "export_time", DateTime.Now.ToString("yyyyMMddhhmmss"));
                InsertTable2Command.ExecuteNonQuery();
            }
        }
        #region IDisposable 成员

        public void Dispose()
        {
            if (IsOpen)
            {
                Close();
            }
            if (InsertTable1Command != null)
            {
                InsertTable1Command.Dispose();
            }
        }

        #endregion
    }
}
